#!/usr/bin/env  python
# coding: utf-8

import pymysql
import datetime
import pandas as pd
from openpyxl.workbook import Workbook
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

#查询mysql获取数据
def fetch_db(sql):
    db_user = pymysql.connect(host="192.168.0.124", port=3306,  user="xx", passwd="xxx", db="statistics")
    #使用cursor() 创建一个游标对象cursor
    cursor = db_user.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    print (result)
    db_user.close()
    return result

#获取sql查询的数据插入到xlsx中，res代表从sql中获取的数据，col表格第一行内容，定位生成表格名称
def gen_xls(res,col,filename):
    file_name = filename + datetime.datetime.now().strftime("%Y-%m-%d") + ".xlsx"
	#从sql获取的内容为元组格式转换为列表
    if type(res) is not list:
        res = list(res)
    #col列表第一行，data为从sql查询出来的数据
    df = pd.DataFrame.from_records(data=res, columns=col)
    df.to_excel(file_name, "Sheet1", index=True, header=True)
    return file_name

#发送带表格附件的邮件
def sendmail(f_email, f_pwd, to_list, smtp_server, sendfile):
	#邮箱对象
	content = '本邮件是每天定时监控，请查收附件，谢谢！'
	msg = MIMEMultipart()
	msg['From'] = f_email
	msg['Subject'] = 'hadoop数据集群资源使用情况'
	to_str = ''
	# for x in to_list:
	#     to_str  += x + ','
	msg['To'] = ",".join(to_list)
	#邮件正文为MIMEtext
	msg.attach(MIMEText(content, 'plain', 'utf-8'))
	with open(sendfile, mode='rb') as fp:
		attfile = fp.read()
	mime = MIMEApplication(attfile)
	mime.add_header('Content-Disposition', 'attachment', filename='%s' %sendfile)
	msg.attach(mime)
	try:
		server = smtplib.SMTP_SSL(smtp_server, 465)
		#设为1打印log
		server.set_debuglevel(0)
		server.login(f_email, f_pwd)
		server.sendmail(f_email, to_list, msg.as_string())
		server.quit()
		print("邮件已经发送成功")
	except smtplib.SMTPException as e:
		print(e)




if __name__ == "__main__":
    tdres = fetch_db('select * from resourceUsage where to_days(current_data) = to_days( now() )')
    tdname = "巡检报表-"
    col = ['巡检时间', '主机ip', 'cpu使用率(%)', '内存使用率(%)', '磁盘使用率(%)']
    file_name = gen_xls(tdres, col, tdname)
    print(file_name)
    from_email = '发送邮件邮箱'
    mail_pass = '密码'
    to_email = ['接受邮箱1', '接受邮箱2']
    smtp_server = 'smtp.163.com'
    sendmail(from_email, mail_pass, to_email, smtp_server, file_name)

